﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;

namespace ComTools
{
    class MySqlHelper
    {
        log4net.ILog log = null;
        IDbConnection _Connection = null;

        public MySqlHelper()
        {
            log = log4net.LogManager.GetLogger(this.GetType());
        }

        public MySqlHelper(string server, int port, string db, string uid, string pwd)
        {
            log = log4net.LogManager.GetLogger(this.GetType());
            SetConn(server, port, db, uid, pwd);
        }

        public void SetConn(string server, int port, string db, string uid, string pwd)
        {
            string conn = $"Server={server};Port={port};Database={db};Uid={uid};Pwd={pwd};";
            _Connection = new MySqlConnection(conn);
        }

        public IDbConnection DBConn
        {
            get
            {
                return _Connection;
            }
        }

        public bool TestDB(out string errmsg)
        {
            errmsg = string.Empty;
            if (_Connection == null)
            {
                errmsg = "连接尚未初始化";
                return false;
            }
            try
            {
                int i = _Connection.ExecuteScalar<int>("SELECT 1");
                return (i == 1);
            }
            catch (Exception exc)
            {
                errmsg = exc.Message;
                return false;
            }
        }

        public DataTable ShowDataBases(out string errmsg)
        {
            DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
            errmsg = string.Empty;
            if (_Connection == null)
            {
                errmsg = "连接尚未初始化";
                return dataTable;
            }
            try
            {
                IEnumerable<string> dra = _Connection.Query<string>("show databases");
                dataTable.Columns.Add("database");
                dataTable.BeginLoadData();
                foreach (string db in dra)
                {
                    dataTable.LoadDataRow(new string[] { db }, true);
                }
                dataTable.EndLoadData();
                dataTable.AcceptChanges();
            }
            catch (Exception exc)
            {
                errmsg = exc.Message;
            }
            return dataTable;
        }

        public DataTable ShowTables(out string errmsg)
        {
            return ShowTables(null, out errmsg);
        }

        public DataTable ShowTables(string database, out string errmsg)
        {
            DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
            errmsg = string.Empty;
            if (_Connection == null)
            {
                errmsg = "连接尚未初始化";
                return dataTable;
            }
            try
            {
                string sql = "show tables";
                if (!string.IsNullOrWhiteSpace(database))
                {
                    sql = "show tables from " + database;
                }
                IEnumerable<string> dra = _Connection.Query<string>(sql);
                dataTable.Columns.Add("table");
                dataTable.BeginLoadData();
                foreach (string db in dra)
                {
                    dataTable.LoadDataRow(new string[] { db }, true);
                }
                dataTable.EndLoadData();
                dataTable.AcceptChanges();
            }
            catch (Exception exc)
            {
                errmsg = exc.Message;
            }
            return dataTable;
        }

        public DataTable ShowColumns(string tableName, out string errmsg)
        {
            DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
            errmsg = string.Empty;
            if (_Connection == null)
            {
                errmsg = "连接尚未初始化";
                return dataTable;
            }
            try
            {
                IEnumerable<TableColumn> dra = _Connection.Query<TableColumn>("show full columns from " + tableName);
                dataTable.Columns.Add("Field");
                dataTable.Columns.Add("Type");
                dataTable.Columns.Add("Comment");
                dataTable.Columns.Add("Collation");
                dataTable.Columns.Add("Null");
                dataTable.Columns.Add("Key");
                dataTable.Columns.Add("Default");
                dataTable.Columns.Add("Extra");
                dataTable.Columns.Add("Privileges");

                dataTable.BeginLoadData();
                foreach (TableColumn db in dra)
                {
                    dataTable.LoadDataRow(new object[] { db.Field, db.Type, db.Comment, db.Collation,
                    db.Null,db.Key, db.Default, db.Extra, db.Privileges}, true);
                }
                dataTable.EndLoadData();
                dataTable.AcceptChanges();
            }
            catch (Exception exc)
            {
                errmsg = exc.Message;
            }
            return dataTable;
        }

        public string Columns2Module(DataTable columnsTable, string className)
        {
            int dotPos = className.LastIndexOf(".");
            string packageName = dotPos > 0 ? className.Substring(0, dotPos) : "";
            string netModuleName = dotPos > 0 ? className.Substring(dotPos + 1) : className;
            StringBuilder sb = new StringBuilder(4000);
            StringBuilder sbHeader = new StringBuilder(500);
            sbHeader.AppendLine("package " + packageName + ";");
            sbHeader.AppendLine();

            List<string> imports = new List<string>();

            StringBuilder sbTail = new StringBuilder(500);

            sb.Append("public class ").Append(netModuleName).AppendLine(" {");
            //
            foreach (DataRow dr in columnsTable.Rows)
            {
                sb.Append("\t").Append("private ");
                //
                string prefix, netType;
                string fieldtype = SplitJavaType2Prefix(dr["Type"].ToString(), out prefix, out netType);
                sb.Append(netType);
                //                
                if (!prefix.StartsWith("java.lang"))
                {
                    imports.Add("import " + fieldtype + ";");
                }
                //
                string fieldName = RemoveUnderline(dr["Field"].ToString()).ToString();
                sb.Append(" ").Append(fieldName);
                //
                sb.AppendLine(";");
                //
                sbTail.AppendLine($"\tpublic {netType} { GetPropertyGetName(fieldName)}() {{");
                sbTail.AppendLine($"\t\treturn this.{fieldName};");
                sbTail.AppendLine("\t}");
                //
                sbTail.AppendLine($"\tpublic void { GetPropertySetName(fieldName)}({netType} {fieldName}) {{");
                sbTail.AppendLine($"\t\tthis.{fieldName} = {fieldName};");
                sbTail.AppendLine("\t}").AppendLine();
            }
            //
            sbHeader.AppendLine(string.Join(Environment.NewLine, imports.Distinct())).AppendLine();
            sb.Insert(0, sbHeader.ToString());
            //
            sb.AppendLine().AppendLine(sbTail.ToString().TrimEnd());
            //
            sb.Append("}");
            //
            return sb.ToString();
        }

        public string GetJavaType(string ty)
        {
            string fieldtype = string.Empty;
            if (ty == "int" || ty.StartsWith("int("))
                fieldtype = "java.lang.Integer";
            else if (ty == "bigint" || ty.StartsWith("bigint("))
                fieldtype = "java.lang.Long";
            else if (ty.StartsWith("tinyint") || ty.StartsWith("tinyint("))
                fieldtype = "java.lang.Integer";
            else if (ty.StartsWith("decimal("))
                fieldtype = "java.math.BigDecimal";
            else if (ty == "double" || ty.StartsWith("double("))
                fieldtype = "java.lang.Double";
            else if (ty == "float" || ty.StartsWith("float("))
                fieldtype = "java.lang.Float";
            else if (ty.StartsWith("varchar(") || ty.StartsWith("char(") || ty == "text" || ty == "tinytext")
                fieldtype = "java.lang.String";
            else if (ty == "datetime" || ty == "date" || ty.StartsWith("datetime(") || ty.StartsWith("date("))
                fieldtype = "java.util.Date";
            else if (ty == "time" || ty.StartsWith("time("))
                fieldtype = "java.sql.Time";
            else if (ty == "timestamp")  //fieldtype = "jxl.write.DateTime";
                fieldtype = "java.sql.Timestamp";
            else if (ty == "binary" || ty == "varbinary" || ty.StartsWith("binary(") || ty.StartsWith("varbinary("))
                fieldtype = "java.lang.Byte[]";
            return fieldtype;
        }

        public string SplitJavaType2Prefix(string type, out string prefix, out string netType)
        {
            var r = GetJavaType(type);
            int i = r.LastIndexOf('.');
            if (i < 0)
            {
                netType = type;
                prefix = string.Empty;
            }
            else
            {
                netType = r.Substring(i + 1);
                prefix = r.Substring(0, i);
            }
            return r; //r.Replace("java.lang.", "");
        }

        public string GetJDBCType(string ty)
        {
            string fieldtype = string.Empty;
            if (ty.StartsWith("int") || ty.StartsWith("tinyint"))
                fieldtype = "INTEGER";
            else if (ty.StartsWith("bigint"))
                fieldtype = "BIGINT";
            else if (ty.StartsWith("bit"))
                fieldtype = "BIT";
            else if (ty.StartsWith("numeric"))
                fieldtype = "NUMERIC";
            else if (ty.StartsWith("blob"))
                fieldtype = "BLOB";
            else if (ty.StartsWith("decimal"))
                fieldtype = "DECIMAL";
            else if (ty.StartsWith("double"))
                fieldtype = "DOUBLE";
            else if (ty.StartsWith("float"))
                fieldtype = "FLOAT";
            else if (ty.StartsWith("varchar") || ty.StartsWith("char") || ty.StartsWith("text") || ty.StartsWith("tinytext"))
                fieldtype = "VARCHAR";
            else if (ty.StartsWith("datetime") || ty == "date" || ty.StartsWith("date(")
                || ty == "timestamp" || ty.StartsWith("timestamp("))
                fieldtype = "TIMESTAMP";
            else if (ty.StartsWith("binary") || ty.StartsWith("varbinary"))
                fieldtype = "VARBINARY";
            return fieldtype;
        }

        public string GetClassName(string tableName)
        {
            StringBuilder sb = RemoveUnderline(tableName);
            if (Char.IsLetter(sb[0]) && Char.IsLower(sb[0]))
            {
                //sb.Append(Char.ToUpper(tableName[0]));
                sb[0] = Char.ToUpper(tableName[0]);
            }
            return sb.ToString();
        }

        public StringBuilder RemoveUnderline(string name)
        {
            StringBuilder sb = new StringBuilder(name.Length);
            bool isNextUpper = false;
            for (int i = 0; i < name.Length; i++)
            {
                if (name[i] == '_')
                {
                    isNextUpper = true;
                    continue;
                }
                else if (isNextUpper)
                {
                    sb.Append(Char.ToUpper(name[i]));
                    isNextUpper = false;
                }
                else
                {
                    sb.Append(name[i]);
                }
            }

            return sb;
        }

        public string GetPropertyGetName(string propertyName)
        {
            if (Char.IsLower(propertyName[0]))
            {
                return "get" + Char.ToUpper(propertyName[0]) + propertyName.Substring(1);
            }
            else
            {
                return "get" + propertyName;
            }
        }
        public string GetPropertySetName(string propertyName) {
            if (Char.IsLower(propertyName[0]))
            {
                return "set" + Char.ToUpper(propertyName[0]) + propertyName.Substring(1);
            }
            else
            {
                return "set" + propertyName;
            }
        }

        public string Table2MapperInterface(DataTable columns, string fullMapperName, string fullModuleName)
        {
            int dotPos1 = fullMapperName.LastIndexOf(".");
            string netMapperName = dotPos1 > 0 ? fullMapperName.Substring(dotPos1 + 1) : fullMapperName;
            string packageName = dotPos1 > 0 ? fullMapperName.Substring(0, dotPos1) : "";
            int dotPos = fullModuleName.LastIndexOf(".");
            string netModuleName = dotPos > 0 ? fullModuleName.Substring(dotPos + 1) : fullModuleName;
            //
            bool useGeneratedKeys = false;
            string keyField = string.Empty;
            string keyProperty = string.Empty;
            string keyJavaType = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                string fieldname = dr["Field"].ToString();
                string propertyName = RemoveUnderline(fieldname).ToString();
                string jdbcName = GetJavaType(dr["Type"].ToString());
                if (!useGeneratedKeys && dr["Key"].ToString() == "PRI")
                {
                    useGeneratedKeys = true;
                    keyField = fieldname;
                    keyProperty = propertyName;
                    keyJavaType = jdbcName;
                    break;
                }
            }
            //
            StringBuilder sb = new StringBuilder(4000);
            sb.AppendLine("package "+ packageName + ";").AppendLine();
            //sb.AppendLine("import java.math.BigDecimal;");
            //sb.AppendLine("import java.util.Date;");
            sb.AppendLine("import java.util.List;");
            sb.AppendLine("import " + fullModuleName+";");
            sb.AppendLine();
            sb.AppendLine("public interface " + netMapperName + " {");
            sb.AppendLine();
            sb.AppendLine("\tint insert(" + netModuleName + " record);");
            sb.AppendLine();
            sb.AppendLine("\tint insertSelective(" + netModuleName + " record);");
            sb.AppendLine();
            sb.AppendLine("\tint updateByPrimaryKey(" + netModuleName + " record);");
            sb.AppendLine();
            sb.AppendLine("\tint updateByPrimaryKeySelective(" + netModuleName + " record);");
            sb.AppendLine();
            sb.AppendLine($"\tint deleteByPrimaryKey({keyJavaType} id);");
            sb.AppendLine();
            sb.AppendLine($"\t{netModuleName} selectByPrimaryKey({keyJavaType} imId);");
            sb.AppendLine();
            sb.AppendLine("\tList<" + netModuleName + "> selectAll();");
            sb.AppendLine();
            sb.AppendLine("}");

            return sb.ToString();
        }

        public string Table2MapperXml(string tableName, string fullMapperName, string fullModuleName)
        {
            string errmsg;
            DataTable columns = ShowColumns(tableName, out errmsg);
            StringBuilder sb = new StringBuilder(9999);
            //
            sb.AppendLine("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
            sb.AppendLine("<!DOCTYPE mapper PUBLIC \" -//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">");

            sb.AppendLine($"<mapper namespace=\"{fullMapperName}\">");
            //
            sb.AppendLine(CreateResultMap(columns, fullModuleName));
            //
            sb.AppendLine(CreateSqlStatement(columns));
            //
            sb.AppendLine(CreateInsert(columns, tableName, "insert", fullModuleName));
            //
            sb.AppendLine(CreateInsertSelect(columns, tableName, "insertSelective", fullModuleName));
            //
            sb.AppendLine(CreateUpdateByPK(columns, tableName, "updateByPrimaryKey", fullModuleName));
            //
            sb.AppendLine(CreateUpdateSelectByPK(columns, tableName, "updateByPrimaryKeySelective", fullModuleName));
            //
            sb.AppendLine(CreateDeleteByPK(columns, tableName, "deleteByPrimaryKey"));
            //
            sb.AppendLine(CreateSelectByPK(columns, tableName, "selectByPrimaryKey"));
            //
            sb.AppendLine(CreateSelectAll(tableName, "selectAll"));
            //
            sb.AppendLine("</mapper>");
            return sb.ToString();
        }

        public string CreateSqlStatement(DataTable columns)
        {
            StringBuilder sb = new StringBuilder(2000);
            sb.AppendLine("\t<sql id = \"Base_Column_List\">");
            sb.Append("\t\t");
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                if (i > 0) sb.Append(",");
                DataRow dr = columns.Rows[i];
                sb.Append(dr["Field"]);
            }
            sb.AppendLine();
            sb.AppendLine("\t</sql>");
            return sb.ToString();
        }

        public string CreateResultMap(DataTable columns, string fullModuleName)
        {
            StringBuilder sb = new StringBuilder(2000);
            sb.AppendLine($"\t<resultMap id=\"BaseResultMap\" type=\"{fullModuleName}\">");
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                string fieldName = RemoveUnderline(dr["Field"].ToString()).ToString();
                string fieldtype = GetJDBCType(dr["Type"].ToString());
                //
                if (dr["Key"].ToString() == "PRI")
                {
                    sb.AppendLine($"\t\t<id column=\"{dr["Field"]}\" jdbcType=\"{fieldtype}\" property=\"{fieldName}\"/>");
                }
                else
                {
                    sb.AppendLine($"\t\t<result column=\"{dr["Field"]}\" jdbcType=\"{fieldtype}\" property=\"{fieldName}\"/>");
                }
                //
            }
            sb.AppendLine("\t</resultMap>");
            return sb.ToString();
        }

        public string CreateInsert(DataTable columns, string tableName, string id, string fullModuleName)
        {
            StringBuilder sbInsName = new StringBuilder(5000);
            StringBuilder sbInsValue = new StringBuilder(5000);
            StringBuilder sb = new StringBuilder(5000);
            bool useGeneratedKeys = false;
            string keyProperty = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                if (!useGeneratedKeys && dr["Extra"].ToString() == "auto_increment")
                {
                    useGeneratedKeys = true;
                    keyProperty = dr["Field"].ToString();
                }
                string fieldname = dr["Field"].ToString();
                string propertyName = RemoveUnderline(fieldname).ToString();
                sbInsName.Append("\t\t\t");
                if (i > 0)
                {
                    sbInsName.Append(",");
                }
                sbInsName.AppendLine(fieldname);
                string jdbcName = GetJDBCType(dr["Type"].ToString());
                sbInsValue.Append("\t\t\t");
                if (sbInsValue.Length > 0)
                {
                    sbInsValue.Append(",");
                }
                sbInsValue.AppendLine($"#{{{propertyName},jdbcType = {jdbcName}}}");
            }
            //
            sb.Append($"\t<insert id=\"{id}\" parameterType=\"{fullModuleName}\"");
            if (useGeneratedKeys)
            {
                sb.Append($" useGeneratedKeys=\"{useGeneratedKeys.ToString().ToLower()}\" keyProperty=\"{keyProperty}\"");
            }
            sb.AppendLine(">");
            sb.AppendLine($"\t\tinsert into {tableName}(");
            sb.Append(sbInsName.ToString());
            sb.AppendLine("\t\t)");
            sb.AppendLine("\t\tvalues(");
            sb.Append(sbInsValue.ToString());
            sb.AppendLine("\t\t)");
            sb.AppendLine("\t</insert>");
            return sb.ToString();
        }

        public string CreateInsertSelect(DataTable columns, string tableName, string id, string fullModuleName)
        {
            StringBuilder sbInsName = new StringBuilder(5000);
            StringBuilder sbInsValue = new StringBuilder(5000);
            StringBuilder sb = new StringBuilder(5000);
            bool useGeneratedKeys = false;
            string keyProperty = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                if (!useGeneratedKeys && dr["Extra"].ToString() == "auto_increment")
                {
                    useGeneratedKeys = true;
                    keyProperty = dr["Field"].ToString();
                }
                string fieldname = dr["Field"].ToString();
                string propertyName = RemoveUnderline(fieldname).ToString();
                sbInsName.AppendLine($"\t\t\t<if test = \"{propertyName} != null\" >{fieldname},</if>");
                string jdbcName = GetJDBCType(dr["Type"].ToString());
                sbInsValue.AppendLine($"\t\t\t<if test = \"{propertyName} != null\" >#{{{propertyName},jdbcType = {jdbcName}}},</if>");
            }
            //
            sb.Append($"\t<insert id=\"{id}\" parameterType=\"{fullModuleName}\"");
            if (useGeneratedKeys)
            {
                sb.Append($" useGeneratedKeys=\"{useGeneratedKeys.ToString().ToLower()}\" keyProperty=\"{keyProperty}\"");
            }
            sb.AppendLine(">");
            sb.AppendLine("\t\tinsert into " + tableName);
            sb.AppendLine("\t\t<trim prefix=\"(\" suffix=\")\" suffixOverrides=\", \">");
            sb.Append(sbInsName.ToString());
            sb.AppendLine("\t\t</trim>");
            sb.AppendLine("\t\t<trim prefix=\"values(\" suffix=\")\" suffixOverrides=\", \">");
            sb.Append(sbInsValue.ToString());
            sb.AppendLine("\t\t</trim>");
            sb.AppendLine("\t</insert>");
            return sb.ToString();
        }

        public string CreateUpdateByPK(DataTable columns, string tableName, string id, string fullModuleName)
        {
            StringBuilder sbUpdName = new StringBuilder(5000);
            StringBuilder sbUpdValue = new StringBuilder(5000);
            StringBuilder sb = new StringBuilder(5000);
            bool useGeneratedKeys = false;
            string keyField = string.Empty;
            string keyProperty = string.Empty;
            string keyJDBCType = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                string fieldname = dr["Field"].ToString();
                string propertyName = RemoveUnderline(fieldname).ToString();
                string jdbcName = GetJDBCType(dr["Type"].ToString());
                if (!useGeneratedKeys && dr["Key"].ToString() == "PRI")
                {
                    useGeneratedKeys = true;
                    keyField = fieldname;
                    keyProperty = propertyName;
                    keyJDBCType = jdbcName;
                }
                else
                {
                    sbUpdName.AppendLine($"\t\t\t{fieldname} = #{{{propertyName},jdbcType = {jdbcName}}},");
                }
            }
            //
            sb.AppendLine($"\t<update id = \"{id}\" parameterType=\"{fullModuleName}\">");
            sb.AppendLine("\t\tupdate " + tableName);
            sb.AppendLine("\t\t<set>");
            //sb.AppendLine("\t\t<trim suffixOverrides=\", \">");
            sb.Append(sbUpdName.ToString());
            //sb.AppendLine("\t\t</trim>");
            sb.AppendLine("\t\t</set>");
            sb.AppendLine($"\t\twhere {keyField} = #{{{keyProperty},jdbcType={keyJDBCType}}}");
            sb.AppendLine("\t</update>");
            return sb.ToString();
        }

        public string CreateUpdateSelectByPK(DataTable columns, string tableName, string id, string fullModuleName)
        {
            StringBuilder sbUpdName = new StringBuilder(5000);
            StringBuilder sbUpdValue = new StringBuilder(5000);
            StringBuilder sb = new StringBuilder(5000);
            bool useGeneratedKeys = false;
            string keyField = string.Empty;
            string keyProperty = string.Empty;
            string keyJDBCType = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                string fieldname = dr["Field"].ToString();
                string propertyName = RemoveUnderline(fieldname).ToString();
                string jdbcName = GetJDBCType(dr["Type"].ToString());
                if (!useGeneratedKeys && dr["Key"].ToString() == "PRI")
                {
                    useGeneratedKeys = true;
                    keyField = fieldname;
                    keyProperty = propertyName;
                    keyJDBCType = jdbcName;
                }
                else
                {
                    sbUpdName.AppendLine($"\t\t\t<if test = \"{propertyName} != null\" >")
                        .AppendLine($"\t\t\t\t{fieldname} = #{{{propertyName},jdbcType = {jdbcName}}},")
                        .AppendLine("\t\t\t</if>");
                }
            }
            //
            sb.AppendLine($"\t<update id = \"{id}\" parameterType=\"{fullModuleName}\">");
            sb.AppendLine("\t\tupdate " + tableName);
            sb.AppendLine("\t\t<set>");
            //sb.AppendLine("\t\t<trim suffixOverrides=\", \">");
            sb.Append(sbUpdName.ToString());
            //sb.AppendLine("\t\t</trim>");
            sb.AppendLine("\t\t</set>");
            sb.AppendLine($"\t\twhere {keyField} = #{{{keyProperty},jdbcType={keyJDBCType}}}");
            sb.AppendLine("\t</update>");
            return sb.ToString();
        }

        public string CreateDeleteByPK(DataTable columns, string tableName, string id)
        {
            StringBuilder sb = new StringBuilder(5000);
            bool useGeneratedKeys = false;
            string keyField = string.Empty;
            string keyProperty = string.Empty;
            string keyJDBCType = string.Empty;
            string keyJavaType = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                if (dr["Key"].ToString() == "PRI")
                {
                    string fieldname = dr["Field"].ToString();
                    string propertyName = RemoveUnderline(fieldname).ToString();
                    string jdbcName = GetJDBCType(dr["Type"].ToString());
                    string javaName = GetJavaType(dr["Type"].ToString());
                    keyField = fieldname;
                    keyProperty = propertyName;
                    keyJDBCType = jdbcName;
                    keyJavaType = javaName;
                    break;
                }
            }
            //
            sb.AppendLine($"\t<delete id = \"{id}\" parameterType=\"{keyJavaType}\">");
            sb.AppendLine("\t\tdelete from " + tableName);
            sb.AppendLine($"\t\twhere {keyField} = #{{{keyProperty},jdbcType={keyJDBCType}}}");
            sb.AppendLine("\t</delete>");
            return sb.ToString();
        }

        public string CreateSelectByPK(DataTable columns, string tableName, string id)
        {
            StringBuilder sb = new StringBuilder(5000);
            string keyField = string.Empty;
            string keyProperty = string.Empty;
            string keyJavaType = string.Empty;
            string keyJDBCType = string.Empty;
            //
            for (int i = 0; i < columns.Rows.Count; i++)
            {
                DataRow dr = columns.Rows[i];
                if (dr["Key"].ToString() == "PRI")
                {
                    string fieldname = dr["Field"].ToString();
                    string propertyName = RemoveUnderline(fieldname).ToString();
                    string jdbcName = GetJDBCType(dr["Type"].ToString());
                    string javaName = GetJavaType(dr["Type"].ToString());
                    keyField = fieldname;
                    keyProperty = propertyName;
                    keyJDBCType = jdbcName;
                    keyJavaType = javaName;
                    break;
                }
            }
            //
            sb.AppendLine($"\t<select id = \"{id}\" parameterType=\"{keyJavaType}\" resultMap=\"BaseResultMap\">");
            sb.AppendLine("\t\tselect <include refid=\"Base_Column_List\"/> ");
            sb.AppendLine("\t\tfrom " + tableName);
            sb.AppendLine($"\t\twhere {keyField} = #{{{keyProperty},jdbcType={keyJDBCType}}}");
            sb.AppendLine("\t</select>");
            return sb.ToString();
        }

        public string CreateSelectAll(string tableName, string id)
        {
            StringBuilder sb = new StringBuilder(5000);
            sb.AppendLine($"\t<select id = \"{id}\" resultMap=\"BaseResultMap\">");
            sb.AppendLine("\t\tselect <include refid=\"Base_Column_List\"/> ");
            sb.AppendLine("\t\tfrom " + tableName);
            sb.AppendLine("\t</select>");
            return sb.ToString();
        }
    }

    class TableColumn
    {
        public string Field { get; set; }
        public string Type { get; set; }
        public string Collation { get; set; }
        public string Null { get; set; }
        public string Key { get; set; }
        public string Default { get; set; }
        public string Extra { get; set; }
        public string Privileges { get; set; }
        public string Comment { get; set; }
    }
}